﻿'===============================================================================
' Microsoft patterns & practices Enterprise Library
' Data Access Application Block QuickStart
'===============================================================================
' Copyright © Microsoft Corporation.  All rights reserved.
' THIS CODE AND INFORMATION IS PROVIDED "AS IS" WITHOUT WARRANTY
' OF ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT
' LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND
' FITNESS FOR A PARTICULAR PURPOSE.
'===============================================================================

Imports Microsoft.Practices.EnterpriseLibrary.Data

' Encapulsates access to the database.
Public Class TransferData

#Region " Ctor "
    ' ctor
    Public Sub TransferData()
    End Sub
#End Region

#Region " Fields "
    ' fields
    Private m_sId As String = String.Empty
#End Region

#Region " Property "
    Public Property Id() As String
        Get
            Return m_sId
        End Get
        Set(ByVal value As String)
            m_sId = value
        End Set
    End Property
#End Region
    
#Region " Function "

    Public Function GetTransfered(ByVal nId As Integer, ByVal sCourseNo As String) As DataSet
        Dim db As Database = DatabaseFactory.CreateDatabase

        Dim sqlCommand As String = "GetTranserferd"
        Dim dbCommand As Common.DbCommand = db.GetStoredProcCommand(sqlCommand)

        db.AddInParameter(dbCommand, "Id", DbType.Int32, nId)
        db.AddInParameter(dbCommand, "CourseNo", DbType.String, sCourseNo)

        Dim ds As DataSet = Nothing

        ds = db.ExecuteDataSet(dbCommand)

        Return ds
    End Function

    Public Function GetTransfered_Out(ByVal nId As Integer, ByVal sCourseNo As String) As DataSet
        Dim db As Database = DatabaseFactory.CreateDatabase

        Dim sqlCommand As String = "GetALLTransfered_Out"
        Dim dbCommand As Common.DbCommand = db.GetStoredProcCommand(sqlCommand)

        db.AddInParameter(dbCommand, "Id", DbType.Int32, nId)
        db.AddInParameter(dbCommand, "CourseNo", DbType.String, sCourseNo)

        Dim ds As DataSet = Nothing

        ds = db.ExecuteDataSet(dbCommand)

        Return ds
    End Function

    ' Retreives all trancourseno in the specified trancourseno.
    ' Category: The Id containing the courseno.
    ' Returns: DataSet containing the courseno.
    ' Remarks: Demonstrates retrieving multiple rows using a DataSet.
    Public Function GetCourseNoInTrancodeNo(ByRef sCourseNo As String, ByVal sId As String) As DataSet

        ' Create the Database object, using the default database service. The
        ' default database service is determined through configuration.
        Dim db As Database = DatabaseFactory.CreateDatabase()

        Dim sqlCommand As String = "GetTrancourseNoByCourseNo"
        Dim dbCommand As Common.DbCommand = db.GetStoredProcCommand(sqlCommand)

        ' Retrieve products from the specified trancourseno.
        db.AddInParameter(dbCommand, "Id", DbType.String, sId)
        db.AddInParameter(dbCommand, "CourseNo", DbType.String, sCourseNo)

        ' DataSet that will hold the returned results		
        Dim TrancourseNoDataSet As DataSet = Nothing

        TrancourseNoDataSet = db.ExecuteDataSet(dbCommand)

        ' Note: connection was closed by ExecuteDataSet method call 

        Return TrancourseNoDataSet
    End Function

    ' Updates the product database.
    ' Returns: The number of rows affected by the update.
    ' Remarks: Demonstrates updating a database using a DataSet.
    Public Function UpdateProducts(ByVal sId As String, ByVal sCourseNo As String, ByVal sTitle As String, _
                                   ByVal sCredit As String, ByVal sTrm As String, ByVal sGrad As String, ByVal sStatus As String, ByVal sProcess As String) As Integer
        ' Create the Database object, using the default database service. The
        ' default database service is determined through configuration.
        Dim db As Database = DatabaseFactory.CreateDatabase()

        Dim Transcourseno_acadDataSet As DataSet = New DataSet

        Dim sqlCommand As String = "Select No, Id, CourseNo, Title, Credit, Trm, Grade, Status, Process " & _
            "From Transcourseno_acad"

        Dim dbCommand As Common.DbCommand = db.GetSqlStringCommand(sqlCommand)

        Dim Transcourseno_tranTable As String = "Transcourseno_acad"

        ' Retrieve the initial data
        db.LoadDataSet(dbCommand, Transcourseno_acadDataSet, Transcourseno_tranTable)

        ' Get the table that will be modified
        Dim table As DataTable = Transcourseno_acadDataSet.Tables(Transcourseno_tranTable)

        ' Add a new product to existing DataSet
        Dim addedRow As DataRow = table.Rows.Add(New Object() {DBNull.Value, sId, sCourseNo, sTitle, sCredit, sTrm, sGrad, sStatus, sProcess})

        ' Modify an existing product
        'table.Rows(0)("ProductName") = "Modified product"

        ' Establish our Insert, Delete, and Update commands
        Dim insertCommand As Common.DbCommand = db.GetStoredProcCommand("AddTranscourseno_acad")
        db.AddInParameter(insertCommand, "Id", DbType.String, "Id", DataRowVersion.Current)
        db.AddInParameter(insertCommand, "CourseNo", DbType.String, "CourseNo", DataRowVersion.Current)
        db.AddInParameter(insertCommand, "Title", DbType.String, "Title", DataRowVersion.Current)
        db.AddInParameter(insertCommand, "Credit", DbType.String, "Credit", DataRowVersion.Current)
        db.AddInParameter(insertCommand, "Trm", DbType.String, "Trm", DataRowVersion.Current)
        db.AddInParameter(insertCommand, "Grade", DbType.String, "Grade", DataRowVersion.Current)
        db.AddInParameter(insertCommand, "Status", DbType.String, "Status", DataRowVersion.Current)
        'db.AddInParameter(insertCommand, "Process", DbType.String, "Process", DataRowVersion.Current)

        Dim deleteCommand As Common.DbCommand = db.GetStoredProcCommand("DeleteTranscourseno_acad")
        db.AddInParameter(deleteCommand, "No", DbType.Int32, "No", DataRowVersion.Current)

        Dim updateCommand As Common.DbCommand = db.GetStoredProcCommand("UpdateTranscourseno_acad")
        db.AddInParameter(updateCommand, "Id", DbType.String, "Id", DataRowVersion.Current)
        db.AddInParameter(updateCommand, "CourseNo", DbType.String, "CourseNo", DataRowVersion.Current)
        db.AddInParameter(updateCommand, "Title", DbType.String, "Title", DataRowVersion.Current)
        db.AddInParameter(updateCommand, "Credit", DbType.String, "Credit", DataRowVersion.Current)

        ' Submit the DataSet, capturing the number of rows that were affected
        Dim rowsAffected As Integer = db.UpdateDataSet(Transcourseno_acadDataSet, "Transcourseno_acad", insertCommand, updateCommand, deleteCommand, UpdateBehavior.Standard)

        Return rowsAffected

    End Function

    Public Function UpdateOut_Transcript(ByVal sId As String, ByVal sCourseNo As String, ByVal sStatus As String) As Integer
        ' Create the Database object, using the default database service. The
        ' default database service is determined through configuration.
        Dim db As Database = DatabaseFactory.CreateDatabase()

        Dim Out_TranscriptTableDataSet As DataSet = New DataSet

        Dim sqlCommand As String = "Select * From Out_Transcript"

        Dim dbCommand As Common.DbCommand = db.GetSqlStringCommand(sqlCommand)

        Dim Out_TranscriptTable As String = "Out_Transcript"

        ' Retrieve the initial data
        db.LoadDataSet(dbCommand, Out_TranscriptTableDataSet, Out_TranscriptTable)

        ' Get the table that will be modified
        Dim table As DataTable = Out_TranscriptTableDataSet.Tables(Out_TranscriptTable)

        ' Add a new product to existing DataSet
        Dim addedRow As DataRow = table.Rows.Add(New Object() {DBNull.Value, sId, sCourseNo, sStatus})

        ' Modify an existing product
        'table.Rows(0)("ProductName") = "Modified product"

        '' Establish our Insert, Delete, and Update commands
        Dim insertCommand As Common.DbCommand = db.GetStoredProcCommand("AddOut_Transcript")
        'db.AddInParameter(insertCommand, "Id", DbType.String, "Id", DataRowVersion.Current)
        'db.AddInParameter(insertCommand, "CourseNo", DbType.String, "CourseNo", DataRowVersion.Current)
        'db.AddInParameter(insertCommand, "Title", DbType.String, "Title", DataRowVersion.Current)
        'db.AddInParameter(insertCommand, "Credit", DbType.String, "Credit", DataRowVersion.Current)
        'db.AddInParameter(insertCommand, "Trm", DbType.String, "Trm", DataRowVersion.Current)
        'db.AddInParameter(insertCommand, "Grade", DbType.String, "Grade", DataRowVersion.Current)
        'db.AddInParameter(insertCommand, "Status", DbType.String, "Status", DataRowVersion.Current)        

        Dim deleteCommand As Common.DbCommand = db.GetStoredProcCommand("DeleteOut_Transcript")
        'db.AddInParameter(deleteCommand, "No", DbType.Int32, "No", DataRowVersion.Current)

        Dim updateCommand As Common.DbCommand = db.GetStoredProcCommand("UpdateOut_Transcript")
        db.AddInParameter(updateCommand, "Id", DbType.String, "Id", DataRowVersion.Current)
        db.AddInParameter(updateCommand, "CourseNo", DbType.String, "CourseNo", DataRowVersion.Current)
        db.AddInParameter(updateCommand, "Status", DbType.String, "Status", DataRowVersion.Current)        

        ' Submit the DataSet, capturing the number of rows that were affected
        Dim rowsAffected As Integer = db.UpdateDataSet(Out_TranscriptTableDataSet, "Out_Transcript", _
                                                       insertCommand, updateCommand, deleteCommand, UpdateBehavior.Standard)

        Return rowsAffected

    End Function

#End Region

End Class
